<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<title>Tutorial: Moving from MySQL to ADODB</title>
</head>

<body bgcolor=white>
<h1>Tutorial: Moving from MySQL to ADODB</h1>

<pre>		You say eether and I say eyether, 
		You say neether and I say nyther; 
		Eether, eyether, neether, nyther - 
		Let's call the whole thing off ! 
<br>
		You like potato and I like po-tah-to, 
		You like tomato and I like to-mah-to; 
		Potato, po-tah-to, tomato, to-mah-to - 
		Let's call the whole thing off ! 
</pre>
<p>I love this song, especially the version with Louis Armstrong and
Ella singing duet. It is all about how hard it is for two people in love
to be compatible with each other. It's about compromise and finding a
common ground, and that's what this article is all about.
<p>PHP is all about creating dynamic web-sites with the least fuss
and the most fun. To create these websites we need to use databases to
retrieve login information, to splash dynamic news onto the web page and
store forum postings. So let's say we were using the popular MySQL
database for this. Your company has done such a fantastic job that the
Web site is more popular than your wildest dreams. You find that MySQL
cannot scale to handle the workload; time to switch databases.
<p>Unfortunately in PHP every database is accessed slightly
differently. To connect to MySQL, you would use <i>mysql_connect()</i>;
when you decide to upgrade to Oracle or Microsoft SQL Server, you would
use <i>ocilogon() </i>or <i>mssql_connect()</i> respectively. What is
worse is that the parameters you use for the different connect functions
are different also.. One database says po-tato, the other database says
pota-to. Oh-oh.
<h3>Let's NOT call the whole thing off</h3>
<p>A database wrapper library such as ADODB comes in handy when you
need to ensure portability. It provides you with a common API to
communicate with any supported database so you don't have to call things
off.
<p>
<p>ADODB stands for Active Data Objects DataBase (sorry computer
guys are sometimes not very original). ADODB currently supports MySQL,
PostgreSQL, Oracle, Interbase, Microsoft SQL Server, Access, FoxPro,
Sybase, ODBC and ADO. You can download ADODB from <a
	href=http://php.weblogs.com/adodb></a><a
	href="http://php.weblogs.com/adodb">http://php.weblogs.com/adodb</a>.
<h3>MySQL Example</h3>
<p>The most common database used with PHP is MySQL, so I guess you
should be familiar with the following code. It connects to a MySQL
server at <i>localhost</i>, database <i>mydb</i>, and executes an SQL
select statement. The results are printed, one line per row.
<pre><font color="#666600">$db = <b>mysql_connect</b>(&quot;localhost&quot;, &quot;root&quot;, &quot;password&quot;);
<b>mysql_select_db</b>(&quot;mydb&quot;,$db);</font>
<font color="#660000">$result = <b>mysql_query</b>(&quot;SELECT * FROM employees&quot;,$db)</font><code><font
	color="#663300">;
if ($result === false) die(&quot;failed&quot;);</font></code> 
<font color="#006666"><b>while</b> ($fields =<b> mysql_fetch_row</b>($result)) &#123;
 <b>for</b> ($i=0, $max=sizeof($fields); $i &lt; $max; $i++) &#123;
		<b>print</b> $fields[$i].' ';
 &#125;
 <b>print</b> &quot;&lt;br&gt;\n&quot;;
&#125;</font> 
</pre>
<p>The above code has been color-coded by section. The first section
is the connection phase. The second is the execution of the SQL, and the
last section is displaying the fields. The <i>while</i> loop scans the
rows of the result, while the <i>for</i> loop scans the fields in one
row.</p>
<p>Here is the equivalent code in ADODB</p>
<pre><b><font color="#666600"> include(&quot;adodb.inc.php&quot;);</font></b><font
	color="#666600">
 $db = <b>NewADOConnection</b>('mysql');
 $db-&gt;<b>Connect</b>(&quot;localhost&quot;, &quot;root&quot;, &quot;password&quot;, &quot;mydb&quot;);</font>
 <font color="#663300">$result = $db-&gt;<b>Execute</b>(&quot;SELECT * FROM employees&quot;);
 </font><font color="#663300"></font><code><font color="#663300">if ($result === false) die(&quot;failed&quot;)</font></code><code><font
	color="#663300">;</font></code>  
 <font color="#006666"><b>while</b> (!$result-&gt;EOF) &#123;
	<b>for</b> ($i=0, $max=$result-&gt;<b>FieldCount</b>(); $i &lt; $max; $i++)
		   <b>print</b> $result-&gt;fields[$i].' ';
	$result-&gt;<b>MoveNext</b>();
	<b>print</b> &quot;&lt;br&gt;\n&quot;;
 &#125;</font> </pre>
<p></p>
<p>Now porting to Oracle is as simple as changing the second line to
<code>NewADOConnection('oracle')</code>. Let's walk through the code...</p>
<h3>Connecting to the Database</h3>
<p></p>
<pre><b><font color="#666600">include(&quot;adodb.inc.php&quot;);</font></b><font
	color="#666600">
$db = <b>NewADOConnection</b>('mysql');
$db-&gt;<b>Connect</b>(&quot;localhost&quot;, &quot;root&quot;, &quot;password&quot;, &quot;mydb&quot;);</font></pre>
<p>The connection code is a bit more sophisticated than MySQL's
because our needs are more sophisticated. In ADODB, we use an
object-oriented approach to managing the complexity of handling multiple
databases. We have different classes to handle different databases. If
you aren't familiar with object-oriented programing, don't worry -- the
complexity is all hidden away in the<code> NewADOConnection()</code>
function.</p>
<p>To conserve memory, we only load the PHP code specific to the
database you are connecting to. We do this by calling <code>NewADOConnection(databasedriver)</code>.
Legal database drivers include <i>mysql, mssql, oracle, oci8,
postgres, sybase, vfp, access, ibase </i>and many others.</p>
<p>Then we create a new instance of the connection class by calling
<code>NewADOConnection()</code>. Finally we connect to the database
using <code>$db-&gt;Connect(). </code></p>
<h3>Executing the SQL</h3>
<p><code><font color="#663300">$result = $db-&gt;<b>Execute</b>(&quot;SELECT
* FROM employees&quot;);<br>
if ($result === false) die(&quot;failed&quot;)</font></code><code><font
	color="#663300">;</font></code> <br>
</p>
<p>Sending the SQL statement to the server is straight forward.
Execute() will return a recordset object on successful execution. You
should check $result as we do above.
<p>An issue that confuses beginners is the fact that we have two
types of objects in ADODB, the connection object and the recordset
object. When do we use each?
<p>The connection object ($db) is responsible for connecting to the
database, formatting your SQL and querying the database server. The
recordset object ($result) is responsible for retrieving the results and
formatting the reply as text or as an array.
<p>The only thing I need to add is that ADODB provides several
helper functions for making INSERT and UPDATE statements easier, which
we will cover in the Advanced section.
<h3>Retrieving the Data<br>
</h3>
<pre><font color="#006666"><b>while</b> (!$result-&gt;EOF) &#123;
   <b>for</b> ($i=0, $max=$result-&gt;<b>FieldCount</b>(); $i &lt; $max; $i++)
	   <b>print</b> $result-&gt;fields[$i].' ';
   $result-&gt;<b>MoveNext</b>();
   <b>print</b> &quot;&lt;br&gt;\n&quot;;
&#125;</font></pre>
<p>The paradigm for getting the data is that it's like reading a
file. For every line, we check first whether we have reached the
end-of-file (EOF). While not end-of-file, loop through each field in the
row. Then move to the next line (MoveNext) and repeat.
<p>The <code>$result-&gt;fields[]</code> array is generated by the
PHP database extension. Some database extensions do not index the array
by field name. To force indexing by name - that is associative arrays -
use the $ADODB_FETCH_MODE global variable.
<pre>
	$<b>ADODB_FETCH_MODE</b> = ADODB_FETCH_NUM;
	$rs1 = $db->Execute('select * from table');
	$<b>ADODB_FETCH_MODE</b> = ADODB_FETCH_ASSOC;
	$rs2 = $db->Execute('select * from table');
	print_r($rs1->fields); // shows <i>array([0]=>'v0',[1] =>'v1')</i>
	print_r($rs2->fields); // shows <i>array(['col1']=>'v0',['col2'] =>'v1')</i>
</pre>
<p>As you can see in the above example, both recordsets store and
use different fetch modes based on the $ADODB_FETCH_MODE setting when
the recordset was created by Execute().</p>
<h2>ADOConnection<a name="ADOConnection"></a></h2>
<p>Object that performs the connection to the database, executes SQL
statements and has a set of utility functions for standardising the
format of SQL statements for issues such as concatenation and date
formats.</p>

<h3>Other Useful Functions</h3>
<p><code>$recordset-&gt;Move($pos)</code> scrolls to that particular
row. ADODB supports forward scrolling for all databases. Some databases
will not support backwards scrolling. This is normally not a problem as
you can always cache records to simulate backwards scrolling.
<p><code>$recordset-&gt;RecordCount()</code> returns the number of
records accessed by the SQL statement. Some databases will return -1
because it is not supported.
<p><code>$recordset-&gt;GetArray()</code> returns the result as an
array.
<p><code>rs2html($recordset)</code> is a function that is generates
a HTML table based on the $recordset passed to it. An example with the
relevant lines in bold:
<pre>   include('adodb.inc.php'); 
   <b>include('tohtml.inc.php');</b> /* includes the rs2html function */
   $conn = &amp;ADONewConnection('mysql'); 
   $conn-&gt;PConnect('localhost','userid','password','database');
   $rs = $conn-&gt;Execute('select * from table');
  <b> rs2html($rs)</b>; /* recordset to html table */ </pre>
<p>There are many other helper functions that are listed in the
documentation available at <a href="http://php.weblogs.com/adodb_manual"></a><a
	href="http://php.weblogs.com/adodb_manual">http://php.weblogs.com/adodb_manual</a>.

<h2>Advanced Material</h2>
<h3>Inserts and Updates</h3>
<p>Let's say you want to insert the following data into a database.

<p><b>ID</b> = 3<br>
<b>TheDate</b>=mktime(0,0,0,8,31,2001) /* 31st August 2001 */<br>
<b>Note</b>= sugar why don't we call it off
<p>When you move to another database, your insert might no longer
work.</p>
<p>The first problem is that each database has a different default
date format. MySQL expects YYYY-MM-DD format, while other databases have
different defaults. ADODB has a function called DBDate() that addresses
this issue by converting converting the date to the correct format.</p>
<p>The next problem is that the <b>don't</b> in the Note needs to be
quoted. In MySQL, we use <b>don\'t</b> but in some other databases
(Sybase, Access, Microsoft SQL Server) we use <b>don''t. </b>The qstr()
function addresses this issue.</p>
<p>So how do we use the functions? Like this:</p>
<pre>$sql = &quot;INSERT INTO table (id, thedate,note) values (&quot; 
   . $<b>ID</b> . ','
   . $db-&gt;DBDate($<b>TheDate</b>) .','
   . $db-&gt;qstr($<b>Note</b>).&quot;)&quot;;
$db-&gt;Execute($sql);</pre>
<p>ADODB also supports <code>$connection-&gt;Affected_Rows()</code>
(returns the number of rows affected by last update or delete) and <code>$recordset-&gt;Insert_ID()</code>
(returns last autoincrement number generated by an insert statement). Be
forewarned that not all databases support the two functions.<br>
</p>
<h3>MetaTypes</h3>
<p>You can find out more information about each of the fields (I use
the words fields and columns interchangebly) you are selecting by
calling the recordset method <code>FetchField($fieldoffset)</code>. This
will return an object with 3 properties: name, type and max_length.
<pre>For example:</pre>
<pre>$recordset = $conn-&gt;Execute(&quot;select adate from table&quot;);<br>$f0 = $recordset-&gt;FetchField(0);
</pre>
<p>Then <code>$f0-&gt;name</code> will hold <i>'adata'</i>, <code>$f0-&gt;type</code>
will be set to '<i>date'</i>. If the max_length is unknown, it will be
set to -1.
<p>One problem with handling different databases is that each
database often calls the same type by a different name. For example a <i>timestamp</i>
type is called <i>datetime</i> in one database and <i>time</i> in
another. So ADODB has a special <code>MetaType($type,
$max_length)</code> function that standardises the types to the following:
<p>C: character and varchar types<br>
X: text or long character (eg. more than 255 bytes wide).<br>
B: blob or binary image<br>
D: date<br>
T: timestamp<br>
L: logical (boolean)<br>
I: integer<br>
N: numeric (float, double, money)
<p>In the above date example,
<p><code>$recordset = $conn-&gt;Execute(&quot;select adate
from table&quot;);<br>
$f0 = $recordset-&gt;FetchField(0);<br>
$type = $recordset-&gt;MetaType($f0-&gt;type, $f0-&gt;max_length);<br>
print $type; /* should print 'D'</code> */
<p>
<p><b>Select Limit and Top Support</b>
<p>ADODB has a function called
$connection->SelectLimit($sql,$nrows,$offset) that allows you to
retrieve a subset of the recordset. This will take advantage of native
SELECT TOP on Microsoft products and SELECT ... LIMIT with PostgreSQL
and MySQL, and emulated if the database does not support it.
<p><b>Caching Support</b>
<p>ADODB allows you to cache recordsets in your file system, and
only requery the database server after a certain timeout period with
$connection->CacheExecute($secs2cache,$sql) and
$connection->CacheSelectLimit($secs2cache,$sql,$nrows,$offset).
<p><b>PHP4 Session Handler Support</b>
<p>ADODB also supports PHP4 session handlers. You can store your
session variables in a database for true scalability using ADODB. For
further information, visit <a
	href="http://php.weblogs.com/adodb-sessions"></a><a
	href="http://php.weblogs.com/adodb-sessions">http://php.weblogs.com/adodb-sessions</a>
<h3>Commercial Use Encouraged</h3>
<p>If you plan to write commercial PHP applications that you want to
resell, you should consider ADODB. It has been released using the lesser
GPL, which means you can legally include it in commercial applications,
while keeping your code proprietary. Commercial use of ADODB is strongly
encouraged! We are using it internally for this reason.
<p>
<h2>Conclusion</h2>
<p>As a thank you for finishing this article, here are the complete
lyrics for <i>let's call the whole thing off</i>.<br>
<br>
<pre>
   Refrain 
<br>
		You say eether and I say eyether, 
		You say neether and I say nyther; 
		Eether, eyether, neether, nyther - 
		Let's call the whole thing off ! 
<br>
		You like potato and I like po-tah-to, 
		You like tomato and I like to-mah-to; 
		Potato, po-tah-to, tomato, to-mah-to - 
		Let's call the whole thing off ! 
<br>
But oh, if we call the whole thing off, then we must part. 
And oh, if we ever part, then that might break my heart. 
<br>
		So, if you like pajamas and I like pa-jah-mas, 
		I'll wear pajamas and give up pa-jah-mas. 
		For we know we 
		Need each other, so we 
		Better call the calling off off. 
		Let's call the whole thing off ! 
<br>
   Second Refrain 
<br>
		You say laughter and I say lawfter, 
		You say after and I say awfter; 
		Laughter, lawfter, after, awfter - 
		Let's call the whole thing off ! 
<br>
		You like vanilla and I like vanella, 
		You, sa's'parilla and I sa's'parella; 
		Vanilla, vanella, choc'late, strawb'ry - 
		Let's call the whole thing off ! 
<br>
But oh, if we call the whole thing off, then we must part. 
And oh, if we ever part, then that might break my heart. 
<br>
		So, if you go for oysters and I go for ersters, 
		I'll order oysters and cancel the ersters. 
		For we know we 
		Need each other, so we 
		Better call the calling off off. 
		Let's call the whole thing off ! 
  </pre>
<p><font size=2>Song and lyrics by George and Ira Gershwin,
introduced by Fred Astaire and Ginger Rogers in the film "Shall We
Dance?" </font>
<p>
<p>(c)2001-2002 John Lim.
</body>
</html>
